# coding = utf-8
from datetime import datetime

from django.db.models import Count
from .models import Article
from django.db import connections


def get_coding(request):
    # 获取文章分类统计信息
    art_post = Article.objects.values('category__cname', 'category_id').annotate(c=Count('*')).order_by('-c')

    # 2.近期文章
    r_rec_post = Article.objects.all().order_by('-create_time')[:3]
    # 获取默认数据库连接
    with connections['default'].cursor() as cursor:
        # 执行原生SQL查询
        cursor.execute(
            'SELECT DATE_FORMAT(create_time, "%Y-%m") AS month, COUNT(*) AS count FROM post_article GROUP BY month ORDER BY count DESC')

        # 处理查询结果
        r_file_post = cursor.fetchall()
        # 将月份字符串转换为 datetime 对象
        r_file_post = [(datetime.strptime(month, "%Y-%m"), count) for month, count in r_file_post]
    return {'art_post': art_post, 'r_file_post': r_file_post, 'r_rec_post': r_rec_post}
